package org.dna.metronomo.persistence;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Utility class to interact with DB at SQL level without the burden of bring up
 * a connection. Use the same connection of ibatis mapper
 *
 * @author andrea
 */
public class DBUtils {
	
	static {
		try {
			Class.forName("org.hsqldb.jdbcDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

    public static interface IDBRunnable {

        public void run(Connection c) throws SQLException;
    }

    public static void cleanTable(final String tableName) throws SQLException {
        DBUtils.rundbTransactionExecutor(new IDBRunnable() {

            public void run(Connection c) throws SQLException {
                Statement s = c.createStatement();
                try {
                    s.execute("DELETE FROM " + tableName);
                } catch (SQLException se) {
                    se.printStackTrace();
                } finally {
                    s.close();
                }
            }
        });
    }

    public static void cleanTable(final String tableName, Connection c) throws SQLException {
        DBUtils.rundbTransactionExecutor(new IDBRunnable() {

            public void run(Connection c) throws SQLException {
                Statement s = c.createStatement();
                try {
                    s.execute("DELETE FROM " + tableName);
                } catch (SQLException se) {
                    se.printStackTrace();
                } finally {
                    s.close();
                }
            }
        }, c);
    }

    public static void cleanAllTables() throws SQLException {
        DBUtils.rundbTransactionExecutor(new IDBRunnable() {

            public void run(Connection c) throws SQLException {
                Statement s = c.createStatement();
                ResultSet rs = c.getMetaData().getTables(null, null, "*", null);
                try {
                    while (rs.next()) {
                        String tableName = rs.getString("TABLE_NAME");
                        s.execute("DELETE FROM " + tableName);
                    }
                } catch (SQLException se) {
                    se.printStackTrace();
                } finally {
                    s.close();
                }
            }
        });
    }

    public static void rundbTransactionExecutor(IDBRunnable task) throws SQLException {
    	//Create a DB connection
		Connection c = DriverManager.getConnection(
				"jdbc:hsqldb:mem:testfixture", "sa", "");
    	
    	
//        Connection c = IBatisDb.sqlMapper.getDataSource().getConnection();
        try {
            task.run(c);
        } finally {
            c.commit();
            c.close();
        }
    }

    public static void rundbTransactionExecutor(IDBRunnable task, Connection c) throws SQLException {
        try {
            task.run(c);
        } finally {
            c.commit();
            c.close();
        }
    }

    public static void printQueryResults(final String query) throws SQLException {
        rundbTransactionExecutor(new IDBRunnable() {

            public void run(Connection c) throws SQLException {
                Statement s = c.createStatement();
                ResultSet rs = null;
                try {
                    rs = s.executeQuery(query);
                    printResultSet(rs);
                } catch (SQLException se) {
                    se.printStackTrace();
                } finally {
                    if (rs != null) {
                        rs.close();
                    }
                    s.close();
                }
            }
        });
    }

    public static void printResultSet(ResultSet rs) throws SQLException {
        //print header
        ResultSetMetaData m = rs.getMetaData();
        int cols = m.getColumnCount();
        for (int i = 1; i <= cols; i++) {
            System.out.print(m.getColumnLabel(i) + ",\t");
        }
        System.out.println();
        while (rs.next()) {
            for (int i = 1; i <= cols; i++) {
                System.out.print(rs.getObject(m.getColumnName(i)) + ",\t");
            }
            System.out.println();
        }
    }
    
    public static List<Map<String, Object>> resultSet2ListTable(ResultSet rs) throws SQLException {
        ResultSetMetaData m = rs.getMetaData();
        int cols = m.getColumnCount();
        List<Map<String, Object>> resTable = new ArrayList<Map<String, Object>>();

        while (rs.next()) {
            Map<String, Object> row = new HashMap<String, Object>(cols);
            for (int i = 1; i <= cols; i++) {
                String colName = m.getColumnLabel(i);
                row.put(colName, rs.getObject(i));
            }
            resTable.add(row);
        }
        return resTable;
    }

    /**
     * Execute the select query and return a list of maps,
     */
    //TODO refactor code equals to printQueryResults changes only one line
    public static List<Map<String, Object>> selectQuery(final String query) throws SQLException {
        final List<Map<String, Object>> res = new ArrayList<Map<String, Object>>();

        rundbTransactionExecutor(new IDBRunnable() {

            public void run(Connection c) throws SQLException {
                Statement s = c.createStatement();
                ResultSet rs = null;
                try {
                    rs = s.executeQuery(query);
                    res.addAll(resultSet2ListTable(rs));
                } catch (SQLException se) {
                    se.printStackTrace();
                } finally {
                    if (rs != null) {
                        rs.close();
                    }
                    s.close();
                }
            }
        });
        return res;
    }
    
    public static void updateQuery(final String query) throws SQLException {
        rundbTransactionExecutor(new IDBRunnable() {

            public void run(Connection c) throws SQLException {
                Statement s = c.createStatement();
                try {
                    s.executeUpdate(query);
                } catch (SQLException se) {
                    se.printStackTrace();
                } finally {
                    s.close();
                }
            }
        });
    }
    
    /**
	 * To use in combination with table clean <code> 
	 *  DBUtils.cleanTable("User");
	 * 	resetKeyColumn("User", "idUser", 0);
	 * </code>
	 * */
	public static void resetKeyColumn(final String tableName,
			final String keyColumn, final int value) throws Exception {
		DBUtils.rundbTransactionExecutor(new IDBRunnable() {

			public void run(Connection c) throws SQLException {
				Statement stmt = c.createStatement();
				String query = String.format("ALTER TABLE %s ALTER COLUMN %s RESTART WITH %s", tableName, keyColumn, value);
				stmt.executeUpdate(query);
			}
		});
	}
}
